import pandas as pd
from collections import defaultdict

# 读取 Excel 文件
df = pd.read_excel('revenue_nanhai_revenue_user_money_log.xlsx')

# 清理列名
df.columns = [col.strip() for col in df.columns]

# 确保时间列为 datetime 类型，并提取日期
df['create_time'] = pd.to_datetime(df['create_time'])
df['date'] = df['create_time'].dt.date

# 按 user_id + money + date 分组，计算每组的重复次数
grouped = df.groupby(['user_id', 'money', 'date'])

refund_map = {}

# 处理每组
for (user_id, money, date), group in grouped:
    count = len(group)
    if count > 1:
        # 多出的才是重复扣费：需要退还 (count - 1) 笔
        refund_amount = (count - 1) * money
        refund_map[user_id] = refund_map.get(user_id, 0) + refund_amount

# 生成 SQL 语句
sql_statements = []

for user_id, refund_money in refund_map.items():
    # 更新余额
    update_sql = f"UPDATE revenue_user SET user_money = user_money + {refund_money:.2f} WHERE user_id = {user_id};"
    sql_statements.append(update_sql)

    # 插入日志记录
    insert_sql = f"""INSERT INTO revenue_user_money_log 
(user_id, money, money_type, remark, status, create_time,create_by) 
VALUES 
({user_id}, {refund_money:.2f}, 1, '重复扣费退回', 1, NOW(),'管理员批量处理');"""
    sql_statements.append(insert_sql)

# 保存到文件
with open("退费SQL语句.sql", "w", encoding="utf-8") as f:
    for stmt in sql_statements:
        f.write(stmt + "\n")

print("✅ 已生成 SQL 语句，保存在 退费SQL语句.sql 文件中")
